or - if you just want days, try
=(Now()-A1)/24
and format as [hh]
this will actually give you the number of days
Rgds, Geoff [blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
sorry Mike - didn't want to jump in but I had the idea about using [hh] as days and it worked so I thought I'd post it
Rgds, Geoff [blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
Mike - I think it'd work for weeks as well - just divide by 168 instead of 24
Rgds, Geoff [blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
But this can also be done w/o having to format the cell (i.e. it can be left at "General":
To get the number of days
=VALUE(TEXT((NOW()-$A$1)/24,"[h]")
To get the number of weeks
=VALUE(TEXT((NOW()-$A$1)/168,"[h]")
Plus this can also be taken further to get the exact number of days (w/ decimal) by using "[h].m" format!
To get the exact number of days (w/ hours as a decimal)
=VALUE(TEXT((NOW()-$A$1)/24,"[h].m")
To get the exact number of weeks (w/ days as a decimal)
=VALUE(TEXT((NOW()-$A$1)/168,"[h].m")
This works with days and weeks because 1 day = 24 hours and 1 week = 168 hours. Unfortunately the months have various number of days and there is a leap year. But I'm sure that with a couple of nested if's you could get it to work for months and years too!!
I think I will add this to my FAQ: faq68-4037 - What are some of Excel's date functions?
Don't wanna get done for back scratching but have a star for the extrapolation of the idea and yes, please do add it to your FAQ
Rgds, Geoff [blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.