How to Solve 4 Frustrating Time Calculations in MS Excel 200

[agbluvsu2]

This is great! However, at my work, our time sheet has four cells for graveyard shifts. So, say someone came in on Wednesday evening at 10pm an left at 6am the next morning. Then came in again Thursday night at 10pm. For Thursday, it would say that they clocked in at 12AM-6AM then in the next two cells it says they clocked back in at 10PM-12AM. Now I c an calculate each individually, but I need to add them up into one big merged cell to show the total hours. How do I do that?

[agbluvsu2]

For example, the cell placement would be cells A1,A2,B1,B2 (for the times) and merged cells C1&2 to show the total amount of hours. It's similar to as if someone had clocked out for a break and then came back

[DannyRocksExcels]

@agbluvsu2

Glad that you enjoyed the video!

Re: Graveyard Shift - aka, Time that Crosses Midnight

Simple answer - use the MOD() Function for this:

=MOD((EndTime - StartTime),1) then, remember to format the Formula Cell as [h]:mm to get the total number of hours worked.

So, without accounting for Meal Breaks, the formula would be:

=MOD((6:00 AM - 10:00 PM),1) - Obviously, you would use cell references rather than these values. Pay attention to the pairs of ().

Danny Rocks

[Pitahchez]

From your explanation I was able to resolve the total sum of time in any array. My frustration while creating a Timesheet was; excel tends to round-off values over the 24hr clock into 1 Day rather than returning a value greater than 24:01:00 for hourly billing.

[DannyRocksExcels]

@Pitahchez

Yes, in order to display the total amount of hours worked, you MUST apply a Custom Format to the cells with the Total Hours worked - Use [h]:mm

This is the biggest challenge when working with Time Calculations.

For Custom Formatting, Right-mouse-click the cell - choose Format Cells. On the Number Tab, Choose Custom from the Category and then type in [h]:mm

Danny Rocks

The Company Rocks

[MrPrios1]

Thanks that was great. It solved two of my issues. One question, is there a way to associate a cell with a date and specific time? For example, I want a cell to show a date and the time to be 14:00. Then the next cells would calculate from that date and time.

Pilo

[DannyRocksExcels]

@MrPrios1

Thanks! I am pleased that I could help you.

Here is a quick thougt for your Date / Time question: 1) Enter =NOW() into a cell to get the current date and time in 1 cell. 2) Copy this and Paste Special Values to turn it into a constant (this is an easy way to ensure that you have the correct format for date and time) 3) Edit the Date and the Time in the Cell or in the Formula Bar to get your Starting Date / Time. From there you can "increment" the date & Time as you wish.

Danny

[pjdeaner]

Danny U Rock.. Great Video..

[DannyRocksExcels]

@pjdeaner

Thank you. I am pleased that you enjoyed my video tutorial.

Danny Rocks

The Company Rocks

[53903]

Danny Rocks

[DannyRocksExcels]

@53903

Thank You!

Danny Rocks

The COmpany Rocks

[springbreezzzz]

you literally rock!!!!!!!!

[DannyRocksExcels]

@springbreezzzz

Thank you! I try my best to "rock" my video and live instruction. I appreciate you taking time to write to me.

Danny Rocks

The Company Rocks

[johnmaxvan]

THANX DANNY

it really helped

i got the formulae on the net but missing part was the space in between am/pm and time

DANNY ROCKS

[DannyRocksExcels]

@johnmaxvan

Great! I am pleased that I could help you with this. Calculating Time differences in Excel can be frustrating - the details really count.

Danny Rocks

The Company Rocks

[johnmaxvan]

THIS FORMULAE ALSO HELPS= B1-A1+IF(A1>B1,1)

FOR EXAMPLE=

=6:05 AM-10:54 PM+IF(10:54 PM>6:05 AM,1)

=7:11

[DannyRocksExcels]

To calculate the amount of time that has elapsed, use this formula: Ending Time - Starting Time. So, in your example, 7:00 PM (Ending Time) - 3:45 PM (Starting Time) is 3:15 - Three Hours and 15 minutes. Be sure to format the cell for your result as "h:mm"

There you go!

Danny Rocks

The Company Rocks

[zandieice012]

wait..it's hard because i have excel on my macbook and never used it.. how would i calculate the time from ...lets say 3:45 - 7pm ? is that like 4 hours and 15 mins? so like...4.2? if i want to add all the hours together? :[

[InnerOrchestra]

he speaks like Agent Smith in the matrix, this is really fun to listen to.

[DannyRocksExcels]

@InnerOrchestra

Hi -

Thanks for posting your comment. I have to admit that I have not yet watched "The Matrix" movie, so I will have to rent it.

I am pleased that I could make this a "fun" video. Learning should be fun in my opinion.

Sincerely,

Danny Rocks

The Company Rocks

[marwan111986]

yes thanx u help me alot

[DannyRocksExcels]

@marwan111986

I am pleased that I could help you. Thank you for writing to tell me this.

Danny Rocks

The Company Rocks

[acura1234567890]

very very helpful thank you!