How to Solve 4 Frustrating Time Calculations in MS Excel 200

My clients have the most difficulty performing Excel "time value" calculations. How about you?

One tip - learn the proper format for a the cell that contains your "time value" calculation.

Watch this short video as I demonstrate each of the 4 frustrations my clients face when calculating "time values" in MS Excel.

I invite you to visit my website -
www.thecompanyrocks.com/excels -
to view all of my Excel Video Lessons.

Category:

Howto & Style

Tags:

• Microsoft
• Excel
• 2003
• Time
• Calculations
• Elapsed
• time
• hours
• work
• crossing
• midnight
• total
• worked
• spreadsheets
• Help
• Tu

[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?

[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

[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]

@pjdeaner

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

Danny Rocks

The Company Rocks

[pjdeaner]

Danny U Rock.. Great Video..

[DannyRocksExcels]

@53903

Thank You!

Danny Rocks

The COmpany Rocks