Time calulations

[rae30]

I know the start time and I know the amount of minutes, however I am having difficulty formulating the End time.

F G H
Start Time Min End Time
11:53 1 ?
11:55 23 ?
15:46 1 ?
23:46 55 ? **NOTE SHOULD REFLECT NEXT DAY
17:47 1 ?
20:15 2 ?


Any assistance would be greatly appreciated

R

 

[jeffreybrown]

Try...

=TIME(HOUR(F1),MINUTE(F1)+G1,SECOND(F1))

 

[T. Valko]

I know the start time and I know the amount of minutes, however I am having difficulty formulating the End time.


23:46 55 ? **NOTE SHOULD REFLECT NEXT DAY

Does that mean you want the result to include a date?

The general approach would be something like:

=start_time + TIME(0,n,0)

Where n = the number of minutes

 

[aprilsea]

I am also having trouble calulating time.
I am using Windows XP, Office 2007

I am trying to calulate total time over a period of days, or even with in a 24 hour time period.
Here is a scenario that I was working on today.

Start date 04/15/2011 8:15:am
End date 4/17/2011 8:00pm 59 hours and 45 minutes (if my math is correct)

I need the total hours and minutes in that range. Each calulation starts at the start date and is calulated in 24 hour time periods. I am doing this in my head and its exhausting.

I am not a wiz at excel, and would appreciate any help I could get.
Thank you

 

[T. Valko]

I am also having trouble calulating time.
I am using Windows XP, Office 2007

I am trying to calulate total time over a period of days, or even with in a 24 hour time period.
Here is a scenario that I was working on today.

Start date 04/15/2011 8:15:am
End date 4/17/2011 8:00pm 59 hours and 45 minutes (if my math is correct)

I need the total hours and minutes in that range. Each calulation starts at the start date and is calulated in 24 hour time periods. I am doing this in my head and its exhausting.

I am not a wiz at excel, and would appreciate any help I could get.
Thank you

As long as the entries are ture Excel dates/times then a simple subtraction formula will work.

Book1
	A	B	C
1	Start	End	Total
2	4/15/2011 8:15 AM	4/17/2011 8:00 PM	59:45
Sheet3

Formula entered in C2:

=B2-A2

Format as [h]:mm

 

[aprilsea]

@T. Valko, thank you soooo much. I have no idea why it works, but I knew it could be done, just not by me.
You're amazing!

 

[T. Valko]

@T. Valko, thank you soooo much. I have no idea why it works, but I knew it could be done, just not by me.
You're amazing!

Even though it doesn't look like it, all you're doing is subtracting one number from another.

You're subtracting the start date/time from the end date/time.

In Excel dates are really just integer numbers formatted to look like a date. These numbers are also known as the date serial number. They are simply the count of days starting from a base date.

The default base date is Jan 1 1900. This is date serial number 1. Each successive day the count increases by 1 such that:

1/1/1900 = 1
1/2/1900 = 2
1/3/1900 = 3
1/4/1900 = 4
1/5/1900 = 5
1/1/1970 = 25569
1/1/2000 = 36526
5/21/2011 = 40684

You can see the date serial number by entering a date in a cell then changing the format of that cell to General.

In Excel time is also just a number that's formatted to look like a time.

In Excel time is the fractional part of a day. A day has the numeric value of 1. Time starts at 12:00 AM and has the numeric value of 0. 12:00 PM is the mid point of a day and has the numeric value 0.5.

12:00 AM = 0
3:00 AM = 0.125
8:15 AM = 0.34375
12:00 PM = 0.5
8:00 PM = 0.833333333333333

Again, You can see the time numeric value by entering a time in a cell then changing the format of that cell to General.

So, a date/time is the date serial number plus the numeric time value

4/15/2011 8:15 AM = date serial number 40648 + numeric time value 0.34375 = 40648.34375

4/17/2011 8:00 PM = date serial number 40650 + numeric time value 0.8333333333 = 40650.8333333333

To find the difference we simply subtract:

40650.8333333333 - 40648.34375 = 2.48958333333576

2.48958333333576 formatted as the time format [h]:mm = 59:45