Forum Discussion
Calculate Total Hours Worked / Night Differential of Multiple Personnel having Different Shifts
As I noted in the workbook, that solution requires Excel 365 or Excel for the web.
Microsoft ended mainstream support for Excel 2007 in October 2012 (and ended extended support in October 2017). Consider getting a newer version, or switching to an alternative product; more than one are free.
The new attached workbook contains alternative formulas. These formulas are not as easy to understand. Microsoft's online documentation no longer identifies what functions were available in Excel 2007, so I am guessing that the functions in these formulas were all supported in Excel 2007.
Still, you could be explicit in how Night Differential is to be calculated. My notes in the new workbook on this calculation are slightly different. (Other formula notes are also different, as different functions were used.)
Thank you for this. Truly appreciate your help. However, when i tried changing details just to check if it will change figures on the relevant cells, i encountered #name? in the said cells.
Also, will it be better if i separate the shift schedule start and end time and not be in one cell? e.g. from 2pm to 10pm in one cell, ill make it 2pm in one cell then 10pm on the next one.
"when i tried changing details just to check if it will change figures on the relevant cells, i encountered #name? in the said cells."
If you want assistance regarding that error, post one of the formulas that you changed. I may be able to spot an unintentionally changed function name or LET variable name, or an invalid cell reference that Excel thinks is a name. (Alternatively, the #NAME? error could indicate that you are using an older version of Excel that does not support the TEXTBEFORE and TEXTAFTER functions. But if that were the case, I expect that you would have seen the error indication immediately upon opening the workbook, before changing the formula. Which version of Excel are you using?)
"will it be better if i separate the shift schedule start and end time"
That depends on what you mean by "better". Will it take less time to recalculate? Yes, but only a trivial amount. Will it take up less space in the workbook? Probably, but only by a small amount. Will it be more flexible, allowing you to put highlighting and/or validation on just one of the two times? Yes. Will it make data entry more prone to errors? You are the person that would best know, as it depends on how you are getting the data into the spreadsheet. I don't have a crystal ball that tells me what you are asking, or what your computing situation is, or what opportunities/problems you might encounter in the future.
But looking at the spreadsheet that you posted on Google Drive, I can say that by putting multiple dates across the width of the spreadsheet, you are making it more difficult to get multi-day summaries and do certain other analyses. The obvious alternative is to store only one date per row, and include a column containing the date (not just a day number), repeating employee names as needed.
thank you for you response. just tried changing the time in first entry clocked out from 12:16am to 2:16 to see if ND value will change. it displayed #name? after.
Excel version i'm using is Microsoft Excel 2007 though.
As I noted in the workbook, that solution requires Excel 365 or Excel for the web.
Microsoft ended mainstream support for Excel 2007 in October 2012 (and ended extended support in October 2017). Consider getting a newer version, or switching to an alternative product; more than one are free.
The new attached workbook contains alternative formulas. These formulas are not as easy to understand. Microsoft's online documentation no longer identifies what functions were available in Excel 2007, so I am guessing that the functions in these formulas were all supported in Excel 2007.
Still, you could be explicit in how Night Differential is to be calculated. My notes in the new workbook on this calculation are slightly different. (Other formula notes are also different, as different functions were used.)