Time Calculation and Conversion

griggsbgsu · Mar 8th, 2002, 05:08 PM

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

Russell Hauf · Mar 8th, 2002, 05:13 PM

Quote:

On 2002-03-08 16:08, griggsbgsu wrote:
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

I don't think that the 24 hour format will work for you either (you'll get a negative number). What I suggest, which I'm sure that you'll find to be a pain, is to enter not only the time but the date. I can't see another way around this, sorry.

Somethink like this:

3/8/2002 5:30 PM 3/9/2002 1:00 AM

-rh

Russell Hauf · Mar 8th, 2002, 05:16 PM

Actually, if you just add 24 to your early morning times it will work (but this would be the 24-hour format you so dread). For example,

6:30 PM 25:00

25:00 being 1 am. Otherwise I think you're going to have to use the date.

Hope I've at least given you some ideas,

Russell

Barrie Davidson · Mar 8th, 2002, 05:19 PM

Quote:

On 2002-03-08 16:08, griggsbgsu wrote:
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 [img]/board/images/smiles/icon_smile.gif[/img]

This formula should work for you:

=TEXT(IF(B1
Regards,

griggsbgsu · Mar 8th, 2002, 05:23 PM

Thanks for the formula, but i entered it and corrected the cell numbers to my correct cell and I still get an error. So, I don't know i'm lost.

Barrie Davidson · Mar 8th, 2002, 05:25 PM

Quote:

On 2002-03-08 16:23, griggsbgsu wrote:
Thanks for the formula, but i entered it and corrected the cell numbers to my correct cell and I still get an error. So, I don't know i'm lost.

Cell A1 equals your start time and B1 equals your end time. What was your error (it worked fine for me)? What values were you using to generate the error?

Russell Hauf · Mar 8th, 2002, 05:25 PM

Good one, Barry! That one works for me. B1 should be your second date's cell, and A1 the first. Try it again.

-rh

griggsbgsu · Mar 8th, 2002, 05:30 PM

Okay I'll give you an example from my sheet.

A1=Time In
B1=Time Out
C1=Formaula: =TEXT(B1-A1, "h:mm")

Okay here is a situation where it works fine.
A1 - 3:15 PM
B1 - 11:22 PM
C1 - Formula result: 8:07
Cool, that works!

Here is a situation where the formula doesn't work.
A1 - 3:50 PM
B1 - 12:38 AM
C1 - Formaul result: VALUE!
Okay, that doesn't work.
These are right from my sheet so if they work for someone else I must be doing something really wrong.

Mark W. · Mar 8th, 2002, 05:31 PM

Or...

=TEXT(B2-A2+(B2

Barrie Davidson · Mar 8th, 2002, 05:32 PM

Quote:

On 2002-03-08 16:30, griggsbgsu wrote:
Okay I'll give you an example from my sheet.

A1=Time In
B1=Time Out
C1=Formaula: =TEXT(B1-A1, "h:mm")

Okay here is a situation where it works fine.
A1 - 3:15 PM
B1 - 11:22 PM
C1 - Formula result: 8:07
Cool, that works!

Here is a situation where the formula doesn't work.
A1 - 3:50 PM
B1 - 12:38 AM
C1 - Formaul result: VALUE!
Okay, that doesn't work.
These are right from my sheet so if they work for someone else I must be doing something really wrong.

Put this formula in C1

=TEXT(IF(B1

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2002, 05:08 PM	#1
griggsbgsu New Member Join Date: Mar 2002 Posts: 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

Mar 8th, 2002, 05:16 PM	#3
Russell Hauf MrExcel MVP Join Date: Feb 2002 Location: Portland, OR USA Posts: 1,374	Actually, if you just add 24 to your early morning times it will work (but this would be the 24-hour format you so dread). For example, 6:30 PM 25:00 25:00 being 1 am. Otherwise I think you're going to have to use the date. Hope I've at least given you some ideas, Russell

Mar 8th, 2002, 05:23 PM	#5
griggsbgsu New Member Join Date: Mar 2002 Posts: 6	Thanks for the formula, but i entered it and corrected the cell numbers to my correct cell and I still get an error. So, I don't know i'm lost.

Mar 8th, 2002, 05:25 PM	#7
Russell Hauf MrExcel MVP Join Date: Feb 2002 Location: Portland, OR USA Posts: 1,374	Good one, Barry! That one works for me. B1 should be your second date's cell, and A1 the first. Try it again. -rh

Mar 8th, 2002, 05:30 PM	#8
griggsbgsu New Member Join Date: Mar 2002 Posts: 6	Okay I'll give you an example from my sheet. A1=Time In B1=Time Out C1=Formaula: =TEXT(B1-A1, "h:mm") Okay here is a situation where it works fine. A1 - 3:15 PM B1 - 11:22 PM C1 - Formula result: 8:07 Cool, that works! Here is a situation where the formula doesn't work. A1 - 3:50 PM B1 - 12:38 AM C1 - Formaul result: VALUE! Okay, that doesn't work. These are right from my sheet so if they work for someone else I must be doing something really wrong.

Mar 8th, 2002, 05:31 PM	#9
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Or... =TEXT(B2-A2+(B2