This may have been asked and answered, but i can"t find it
Column A has "Goal" (number of minutes formatted as a number)
Column B has a "Start Time" (formatted as military time)
Column C has "End Time" (formatted as military time)
Column D has "Elapsed Time"
Column E is the difference between the Elapsed Time (Column D) and the Goal (column A).
Any ideas on how to accomplish this?
Thank you for your consideration.
Respectfully,
Ken
Time Calculations, Formula and Formats
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time Calculations, Formula and Formats
By military time, do you mean (for example) 1320, or 13:20 ?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time Calculations, Formula and Formats
If military time is for example 1320:
In D2:
=60*INT(C2/100)+MOD(C2,100)-60*INT(B2/100)-MOD(B2,100)
If B2 could be before midnight and C2 after midnight:
=60*INT(C2/100)+MOD(C2,100)-60*INT(B2/100)-MOD(B2,100)+1440*(C2<B2)
In E2:
=D2-A2
In D2:
=60*INT(C2/100)+MOD(C2,100)-60*INT(B2/100)-MOD(B2,100)
If B2 could be before midnight and C2 after midnight:
=60*INT(C2/100)+MOD(C2,100)-60*INT(B2/100)-MOD(B2,100)+1440*(C2<B2)
In E2:
=D2-A2
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time Calculations, Formula and Formats
If military time is for example 13:20:
In D2:
=1440*(C2-B2)
Format D2 as a number. If B2 could be before midnight and C2 after midnight:
=1440*(C2-B2+(C2<B2))
In E2:
=D2-A2
In D2:
=1440*(C2-B2)
Format D2 as a number. If B2 could be before midnight and C2 after midnight:
=1440*(C2-B2+(C2<B2))
In E2:
=D2-A2
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 308
- Joined: 24 Feb 2010, 13:41
Re: Time Calculations, Formula and Formats
Hans,
Sorry, Military for me is 13:32
Sorry, Military for me is 13:32
-
- 3StarLounger
- Posts: 308
- Joined: 24 Feb 2010, 13:41
Re: Time Calculations, Formula and Formats
Oops, you responded with solutions for both.
Thank you Hans!
Thank you Hans!
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time Calculations, Formula and Formats
An alternative formula for D2 is
=1440*MOD(C2-B2,1)
Again, format D2 as a number.
=1440*MOD(C2-B2,1)
Again, format D2 as a number.
Best wishes,
Hans
Hans