Excel formula timesheet - IF statement

Question

I am building a time sheet in Excel (see image for headings)Excel time sheet

In the column with heading Time +/- I am trying to write a formula to calculate that if the time worked (calculated from the Time Worked column AB) is above or below round the time to the nearest minute and take away 8 hours, as this is the standard working day.

I have come up with this

=IF(AA2>TIME(8,0,0),(ROUND($AA2*1440,0)/1440),IF(AA2<TIME(8,0,0),(ROUND($AA2*1440,0)/1440)))-TIME(8,0,0)

The formula does what I want it to, but it doesn't seem right, any suggestions as to how to make it better?

pnuts · Accepted Answer · 2018-05-09T14:48:49.250

1

If using the 1904 date system then the following should work:

=-1/3+ROUND(AA2*1440,0)/1440

This takes the difference between ROUND(AA2*1440,0)/1440 (already familiar to OP) and 8 hours. Since dealing with date/time indices (where a day is 1), 8 hours may be expressed as 1/3.

edited May 09 '18 at 14:48

answered May 09 '18 at 00:52

pnuts

58,317
11
87
139

1

Can you explain to me how this code gives the same result? I can't understand it (but it is giving me the same result) – treetree May 09 '18 at 14:45
1

I get it now! Thank you for explaining @pnuts – treetree May 12 '18 at 17:08

score 0 · Answer 2 · answered May 09 '18 at 00:56

You're using (ROUND($AA2*1440,0)/1440) several times in your formula. You could calculate this once in a separate column and just refer to the column several times in your formula. The column could even be hidden so it doesn't add noise to your spreadsheet. Could also do it in a macro function and just call the macro function from your formula. An example of how to do that can be found here: How to call VBA function from Excel cells (2010)?

Excel formula timesheet - IF statement

2 Answers2