Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Elapsed Time 5
- Thread starter coder1964
- Start date
-
2
- #2
-
3
- #3
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
=(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
- Thread starter
- #4
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
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
![[cheers]](/data/assets/smilies/cheers.gif "[cheers] [cheers]")
's for you!- Thread starter
- #8
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
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
Genius Geoff!!! Pure and Simple!!!
for you!!!
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?
Peace!![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
Mike
Never say Never!!!
Nothing is impossible!!!
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?
Peace!
Mike
Never say Never!!!
Nothing is impossible!!!
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
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
- Status
Similar threads
