Time Question

Grimbo2k

Board Regular
Joined
Sep 28, 2004
Messages
74
Hi all
Hopefully this is a simple question

I have a date in the future and I need a formula which tells me how many days/hours/mins/secs remain based on current time/date

ie

10 days, 4 hours, 3 mins, 20 seconds remaining

Had a quick play with it and got stuck !

Any thoughts much appreciated

Regards
Grimbo
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

A1 = 28/09/2006 12:00

B1 = 29/09/2006 12:30

D1 = =(B1-A1)*1440*60

E1 (days) = =ROUNDDOWN((D1/86400),0)

F1 (hours) = =ROUNDDOWN((D1-(E1*86400))/3600,0)

G1 (mins) = =ROUNDDOWN((D1-(E1*86400)-(F1*3600))/60,0)

H1 (secs) = =ROUNDDOWN((D1-(E1*86400)-(F1*3600)-(G1*60))/60,0)


you could combine these into one cell, with "days", "mins" etc added - but its nearly lunch.

this is the one cell formula

=ROUNDDOWN(((B1-A1)*1440*60/86400),0)& " days "&ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)&" hours "& ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)*3600))/60,0)&" mins "&ROUND((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)*3600)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400)-(ROUNDDOWN(((B1-A1)*1440*60-(ROUNDDOWN(((B1-A1)*1440*60/86400),0)*86400))/3600,0)*3600))/60,0)*60),0) & "secs"

I'm sure thes is an easier way though (is Aladin about) or anyone else?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
With your future date in A1

=INT(A1-NOW())&" days,"&TEXT(A1-NOW(),"h \hour\s, m \mi\n\s, s")&" seconds remaining"

although it won't update automatically, only when worksheet is recalculated (which you can initiate by hitting F9)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,094
Messages
5,576,083
Members
412,697
Latest member
ahem27
Top