Date formatting

[Nico]

Hi,
I have this sheet filled with dates(hours & minutes worked per
week/month/year) formatted for 24 hours/day using the standard date system.
Now my employer wants me to make that same sheet to format those dates in
workdays.
Now I know I can just make extra columns and put a formula in there with the
proper formatting for this.
But I would like to know if there is a way to format a date into a workday.
Example:
Date is 1day, 3 hours and 0 minutes. Meaning that employee worked for
27(24+3) hours.
Of course that employee didn't work one workday, he worked 3 workdays and 3
hours.
Is there a way to do this with formatting only?
Or do I have to make a copy of my columns with appropriate conversion
functions to show the correct hours & workdays worked?

Thanks in advance,

Nico.

 

[Bob Umlas]

You can divide by 8 and apply the format of
0 ?/8
This will show
3 3/8
does that work?
Bob Umlas
Excel MVP

 

[Nico]

I can't really do that on my input sheet, without making it a bit messy. For
now, I made the conversion from 24 hour days to 8 hour workdays on an
external excel-workmap. Now I noticed that the sum of times gets faulthy if
it exceeds the time within a month. To give one example, if I count all
workdays from January til March from, I end up at the 2nd of March. That
means I have 59 days, cause of Windows-datesystem - which totally does not
regard the 400 year rule which dictates every century has 28 days in
February unless it's dividable by 400 -, it's 60 days. However, when I
manually calculate the sum with the Windows Calculator, I end up at 67 days
instead of 60 days. Could you please give me an explanation & solution for
this?

 

[Pete_UK]

Dates are counted as elapsed days since 1st Jan 1900, but there is a
known bug in Excel's date system which treats 1900 as a leap year
(apparently this was a bug in Lotus 123 and Microsoft allowed it to
continue for "compatibility" reasons). This explains why you are one
day out. I don't understand why you should be 7 days out by using the
calculator.

One way around this may be to switch to the 1904-date system, which
does not suffer from the leap-year bug.

Hope this helps.

Pete