CODEWITHSUNDEEP
excelexcel-2007excel-formula
Danny Beckett
Danny Beckett

Reputation: 20748

Hours to working days

Where I work I don't get paid overtime, but I accrue holiday days for the overtime I work. I have the following spreadsheet which calculates how much overtime I've done and totals it in D15.

Now I want to calculate how many days this is, based on 8 hours per day. In D16, I've done =D15/8 and formatted it as h.mm \d\a\y\s, but this shows as 2.26 days instead of 2.4375 days.

What is the correct formula to use in D16?

enter image description here

Upvotes: 3

Views: 28341

Answers (3)

Daniel Smith
Daniel Smith

Reputation: 11

Make sure that D15 has a number format of [h]:mm then have D16 as =sum(D15/"8:00") should work fine thats what i have tracking my annual leave, I work 37h pw with a leave day being classed as 7h24m or a half day of leave as "3:42"

I have leave taken as a cumulative figure assigned as [h]:mm in cell K2 of my spreadsheet then I have K3=SUM(K2/"7:24") for days taken formatted as a general number

you may also need to change the date datum in excel to the 1904 date system http://support.microsoft.com/kb/182247/en-gb to get this to work (only a problem if you have negative time as I do when calculating flex hours)

Upvotes: 1

JMax
JMax

Reputation: 26591

Note to reader: this question led to multiple solutions some of which were discussed in the comments. Here is a summary of the solution found.

First solution

=(HOUR(D15)+MINUTE(D15)/60)/8

Explanation

Dates and time in Excel are stored as serial numbers, so 19:30 is actually 0.8125.
So, if you divide it by 8, you will get 0.1015625.
This latter value is worth 2.26 days

OP's version (thanks to Danny Becket (OP)) - see the comments below.
This solution now handles hours > 24.

=((DAY(D20)*24)+HOUR(D20)+(MINUTE(D20)/60))/8

or better (credits to Barry Houdini):

=((INT(D20)*24)+HOUR(D20)+(MINUTE(D20)/60))/8

The former formula has a limitation for large values, perhaps not relevant here but if D20 is 800:00 then you get the wrong answer (7 days rather than 100 days). This is probably because DAY function is giving you calendar day which will "reset" at 31, best to use INT in place of DAY.

Another easily understandable version

Divide by the length of the day as a time value:

=D15/"8:00"

More easily changed if length of workday changes

Upvotes: 5

pnuts
pnuts

Reputation: 59460

Enter:

in B3 8:3
in C3 16:3
in D3 =IF(B3<C3,C3-B3-1/3,2/3-B3+C3)

Select B3:D3, format as hh:mm and copy down as far as required.

Sum ColumnD and append *3 to the formula, but format as Number.

Add data by overwriting cells in ColumnB and/or ColumnC as required (defaults do not add to total).

  • Copes with overtime up to next regular start time (ie including past midnight, new serial number). 1/3 because standard working day is 8 hours (24 hours is unity for date serial counter). B3 and C3 could be hard coded but (i) there is no need and (ii) allows more flexibility. If to readily identify non standard start/finish could use conditional formatting.

  • Does not address weekend overtime but could easily be adapted to do so (eg add column, flag weekend day with 8 in that extra column then add that 8 [1/3] to the finish time).

Upvotes: 2

Related Questions