Time Calculation and Conversion

griggsbgsu

New Member
Joined
Mar 7, 2002
Messages
6
Okay, I'm fairly good with Excel, but I'm no expert. I've ran into a wall and I can't figure out what to do, so hopefully someone can help. Feel free to email me: saragrigsby@yahoo.com

Here is my problem. I have a worksheet setup where I keep track of the hours that I work and the pay I receive. It's worked quite well for the most part, but there is one case that doesn't work.

I have it setup so I enter my clock-in time and my clock-out time and it calculates the number of hours I worked and then calculates my pay and so on. The problem is with calculating the hours that I've worked. I enter my clock-in time in one cell, my clock-out time in another, and here is the formula I use to calculate the hours: =TEXT(B2-A2, "hh:mm"). I enter my times in this format: 5:30 PM for example.

Here is where I run into problems. The formula works fine when my hours are in the same day, for example: 5:30 PM to 10:30 PM. BUT, if my hours are say: 5:30 PM to 12:30 AM, then I get an error in my formula. This happens when my clock-out time runs past 12AM.

I've gotten suggestions to use the 24 hour time format: 15:30, but I'd rather use the standard 12 hour format. I've searched everywhere. I really hope someone has some suggestions for me. Thank you :)
 
Nope I didn't get that formula to work either. Are you using 12 hour time format or 24 when you did this formual?? Maybe that's why it's not working for me.

Thanks so far for everyone's responses!
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
On 2002-03-08 16:32, griggsbgsu wrote:
Nope I didn't get that formula to work either. Are you using 12 hour time format or 24 when you did this formual?? Maybe that's why it's not working for me.

Thanks so far for everyone's responses!

Formatting doesn't matter. All time values are stored as fractions of a day regardless of format.
 
Upvote 0
Yeah! It worked! But there is still the little green triangle in the corner because it says the formula is inconsistent. Know how I get rid of that?
 
Upvote 0
I got rid of it, you just ignore the error, easy enough! Thanks so much for the help! And I got answers so fast! You guys rock! I'll be back if I need help again. Thank you!
 
Upvote 0
Subtracting minutes in hh:mm format, and set hours

I see how this works, and it's great, but now I have two additional problems. I want to subtract a lunch time (not always an hour) and calculate overtime. I want to be able to put my lunch in the hh:mm format - 0:45 for 45 minutes, which I think I've figured out (see below). I also want to calculate overtime by subtracting a blank 8 hours from my total hours cell. How could I accomplish this?

I have all cells in the hh:mm format

I have a1=time in
b1=lunch length
c1=time out
d1=total hours [ =SUM((C1-A1)-(B1)) ]
e1=overtime
 
Upvote 0
Welcome to MrExcel.
Put this in E1
=MAX(D1-8,0)

FYI, in future you should post a question in a new topic and reference this topic in your question. :wink:

Best regards,
 
Upvote 0
I have a question along the same line. If it is ok to post here, I will. If I need to start a new thread then let me know
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top