24 hour per deim report in Excel.

[TimT]

I need to setup an Excel report that will allow the use of a 24 hour pay
period so that the time entered as 0:00 at the start of a day will run for 24
hours and end at 0:00 the next day. Any help?
Thanks

 

[Chip Pearson]

I think you need to be a bit more specific with your question.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[TimT]

Ok, I am using the time function in Excel to calculate Per Diem. I have the
tables set up for the different rates, and have everything working except for
the time entry. The company I work for pays per diem on a 24 hour basis when
away from home. When I enter the time 0:00 in column A, Excel sees that as
12:00 midnight. The next column I enter 24:00, Excel also sees that as 12:00
midnight and subsequently calculates the time as 0:00, not the 24 hour period
I need it to. I can enter the column B time as 23:59, and the amount paid
comes out $.02 short. 2 cents isn't much, but when you add it up 90 days at a
time the total starts showing up.

 

[Myrna Larson]

To get the results you want, you need to include the date as well as the time,
i.e.

9/3/2004 0:00 AM as the start time, 9/4/2004 0:00 AM as the end time

 

[TimT]

Maybe my total column is where my problem is. I'm using =(TEXT(H17-G17,
"h:mm")) to return my answer. If I use the date as 9/4/2004 0:00 AM in
column H17 and then 9/5/2004 0:00 AM in column G17, I get a return of 0:00 in
the total column. If I just enter the time in H17 as 0:00 and then enter
12:00 in G17, I get a return of 12:00. Then the next line I enter at H17,
12:00 and then G17 use the date 9/5/2004 12:00 AM I also get a return of
12:00. What I'm looking for is a way to do a single line entry that will
return a 24 hour total, or less if the entries require.