Comments on Yogesh Gupta's Excel Tips: Excel Formula : Calculating Age

hi there i recently created a worksheet for paid a...

2012-01-13T18:27:57.360+05:30

hi there
i recently created a worksheet for paid and unpaid invoices and just need to know if i can somehow put a tick and a cross sign showing whether it has been paid or not.
If so please help me out.
Regards,
Shishir Sharma

I'm using the =INT((TODAY()-AB2)/365.25). It d...

2011-12-22T22:43:49.582+05:30

I'm using the =INT((TODAY()-AB2)/365.25). It does give me the age, but if I don't have a date in the birthday cell(AB2) I get a "111" in the age cell.
How can I make the age cell blank if there is no date in the birthday cell? Thanks!

sir, my self anoop singh. I want to pint a check b...

2011-12-16T13:02:24.513+05:30

sir, my self anoop singh. I want to pint a check book in excel work sheet, but i hve sme prblm like i want to print numeric value in text. Supose 120460.But spellcurr()formula RETURN value one hudred twnty thousnd four hundrd sixty only. But I want print "One lack twenty thounsd four hundrd sixty only"
I hope Sir u help me better.

Sir, My self santosh I want to some information ...

2011-12-08T14:36:56.968+05:30

Sir,

My self santosh I want to some information about my excel sheet simplifying.

Try This ;) =DATEDIF(A1,TODAY(),"y") &a...

2011-11-30T11:53:14.190+05:30

Try This ;)

=DATEDIF(A1,TODAY(),"y") & " years " & DATEDIF(A1,TODAY(),"ym") & " months " & DATEDIF(A1,TODAY(),"md")+1 & " days "

Dear SIr, can we calculate age by the following fo...

2011-04-23T10:30:37.817+05:30

Dear SIr,
can we calculate age by the following formula. Please answer me. (put DOB at Cell A1)

=+DATEDIF(A1,NOW(),"Y")&" Years, "&DATEDIF(A1,NOW(),"ym")&" Months, "&DATEDIF(A1,NOW(),"md")&" Day."

From: Sujit Dhar
sujit_dhar1025@hotmail.com

2011-04-23T10:28:36.473+05:30

This comment has been removed by the author.

Hai Sir, you done great job for intermediate learn...

2010-11-24T09:41:56.400+05:30

Hai Sir, you done great job for intermediate learner for excel we want to some accounting templates like daybook, ledger and balance sheet:
once we enter the transaction that can automatically updated in ledger and balance sheet please send me my mail id is razaravi@gmail.com

hi yogesji, i have an excel sheet having multiple ...

2010-07-10T14:59:36.391+05:30

hi yogesji, i have an excel sheet having multiple data and every data row have a blank row beetween two rows. i want to delete the blank row in one shot, is it possible pls. help me.

Hello Yogesh ji, thanks for the formula, its work...

2010-06-29T13:27:43.456+05:30

Hello Yogesh ji,

thanks for the formula, its working.

@Rick,

thanks

Hi Amit You can use following formula to get the ...

2010-06-26T22:22:55.456+05:30

Hi Amit

You can use following formula to get the last entered numeric value in column A of sheet1

=INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A))

I hope you will be able to modify this to get value in column B

Regards//Yogesh Gupta

I'm not sure why you posted this particular qu...

2010-06-26T20:49:00.552+05:30

I'm not sure why you posted this particular question under this blog article. However, assuming there are **no** blank cells in Column A (which I'm assuming will always have the last value on the sheet in it), then try this formula...

=LOOKUP(2,1/(A1:A65535<>""),A:A)+INDEX(B2:B10,COUNTA(A:A))

Rick Rothstein (MVP - Excel)

Hello Yogesh ji, I need a help from you. I want ...

2010-06-26T19:16:04.577+05:30

Hello Yogesh ji,

I need a help from you.

I want to put a formula in sheet2 which should calculate as per the last (filled) row of sheet1.

for eg.
in sheet1 Cell A61 is 408 & B61 is 203... I want to show the sum of these two in the Cell A12 of sheet2.. but I want the firmula to use the lastest row of Sheet1... say if A62 is 410 & B62 is 200 then A12 of sheet2 should show "610" and like wise everytime there is new entry in sheet1.

hope I am clear in my question.

Thanks

@Squash DATEDIF works in 2003 and 2007 as well. It...

2010-06-01T18:50:29.904+05:30

@Squash DATEDIF works in 2003 and 2007 as well. It is just that it is not a regular excel function. It is a VBA function and hence there is no help available in excel and the argument directions don't turn up.

Hello Mr Gupta Following is not very short but co...

2010-06-01T17:14:31.014+05:30

Hello Mr Gupta

Following is not very short but comes close to figure out the remaining nights sleep till the next birthday. Here C1=date today b2 = date of birth and d2 = calculated birthday this year
=IF(MONTH($C$1)<MONTH(B2);"still "&(DATEVALUE(D2))-TODAY()&" nights sleep";IF(AND(MONTH($C$1)=MONTH(B2);DAY($C$1)<DAY(B2));"still "&(DATEVALUE(D2))-TODAY()&" night(s) sleep";IF(AND(MONTH($C$1)=MONTH(B2);DAY($C$1)=DAY(B2));"**Heep Heep Huray !!!!**";"still "&EDATE(Datevalue(D2);12)-TODAY()&" nights sleep")))

jialin: pretty good, but don't think DATEDIF i...

2010-06-01T16:36:39.261+05:30

jialin: pretty good, but don't think DATEDIF is in Excel 2007. It was in earlier versions, right? Perhaps something similar can be accomplished with DAYS360?

I was thinking something along these lines:
=TEXT(TODAY()-A1,"YYYY")-1900&" Years "&MONTH(TODAY()-A1)-1&" Months "&DAY(TODAY()-A1)&" Days"

Still, all of our formulas needed to be modified to include logic for singular values and zero values for all three: years, months, and days.

A shorter formula: =CONCATENATE(DATEDIF(A1,TODAY(...

2010-05-31T23:26:44.800+05:30

A shorter formula:

=CONCATENATE(DATEDIF(A1,TODAY(),"y")," years ",MOD(DATEDIF(A1,TODAY(),"m"),12), " Months")