I need to find a formula that calculates how much time falls into a certain criteria.

[Aardigspook]

Firstly, apologies for the long post coming up, but I think this needs some explanation, just in case you want to change anything in the future.

Summary
See attached file, which has three different options for you, depending on how much detail you want to see - look at the sheets AS1, AS2 and AS3 for the options.

Full Explanation

The attached file has 5 worksheets.
1. The first (Ark1) is your original - with a correction to the Total Pay Sum.
2. The second (PayRates) is a copy of your time periods and rates of pay for those periods. I separated these out on to a different sheet for ease of making 'Names' - which will become clear later.
3. The third (AS1), fourth (AS2) and fifth (AS3) all give the final result you want - the Total Pay - but with different levels of visibility of what's going on. More details later.

On each of the AS1/2/3 sheets, I've put in some additional rows to show examples of the different time periods being worked out.


Before I go into the details of formulae, let's cover the 'Names'.
'Names' (or 'Named Ranges') are used in Excel to represent references, values or formulae with a name, rather than typing them every time (see here for details: https://support.office.com/en-gb/art...2-ABD7FF379C64).
I've defined 11 Names - each starts with NR_ to indicate that it's a Named Range. 6 of them are the time periods for each rate of pay and 5 are the pay rates for those periods:

NR_MFEveStart	- set as 0.75 which is the numerical equivalent of 18:00
NR_MFEveEnd	- set as 1 which is the numerical equivalent of 24:00
NR_SatEveStart	- set as MOD(TIME(14,0,0),1) which gives the exact numerical equivalent of 14:00 (0.5833...)
NR_SatEveEnd	- set as 1 which is the numerical equivalent of 24:00
NR_NightStart	- set as 0 which is the numerical equivalent of 00:00
NR_NightEnd	- set as 0.25 which is the numerical equivalent of 06:00
NR_BasePay	- equals PayRates!$D$3 (€ 13.74)
NR_MFEvePay	- equals PayRates!$D$4 (€  0.75)
NR_SatEvePay	- equals PayRates!$D$5 (€  2.05)
NR_NightPay	- equals PayRates!$D$6 (€  1.50)
NR_SunPay	- equals PayRates!$D$7 (€  2.35)

So, for example, if a formula needs to check if a time is later than 18:00, it checks if the time is > NR_MFEveStart.
You can change the names by clicking 'Name Manager' on the 'Formulas' tab (sorry, I don't know what they're called in Danish!). Click 'Edit' to change the name title or reference. If you change the name title, you'll need to do a 'Find and Replace' for all references to it in the formulae on the other sheets.

Now let's look at the other sheets and formulae which calculate the time periods and pay.

First sheet: AS1, which shows the hours worked in each time period, the pay for each period and the total pay (a simple sum of the previous columns).

The formulae to calculate the hours for each time period are below. Each starts with a check to see that there's both a start date/time and an end date/time - if not, a blank is returned (that's the IF(OR(C4="",D4=""),"", part).
Start and end times are calculated by using MOD on the date/time in columns C and D. Days of the week are checked by using WEEKDAY - if it's just to check that the start and end days are the same, then just WEEKDAY() is used - if it's to check the actual day of the week, WEEKDAY(ref,1)=1/2/3/4/5/6/7 is used (1=Sunday, 7=Saturday).


The Total hours formula is a simple subtraction of start date/time from end date/time:

=IF(OR(C4="",D4=""),"",D4-C4)

The formula to calculate evening hours Mon-Fri ('M-F eve hours') is substantially longer. It variously checks the weekdays of the start and end dates, then checks the start and end times to work out how much of the time worked falls within the 1800-2400 period on Mondays to Fridays:

=IF(OR(C4="",D4=""),"",IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)< NR_MFEveStart,MOD(D4,1)> NR_MFEveStart,MOD(D4,1)<=NR_MFEveEnd),MOD(D4,1)-NR_MFEveStart,IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)>=NR_MFEveStart,MOD(D4,1)<=NR_MFEveEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)< NR_MFEveStart,MOD(D4,1)< NR_MFEveStart),NR_MFEveEnd-NR_MFEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)>=NR_MFEveStart,MOD(D4,1)<=NR_MFEveStart),NR_MFEveEnd-MOD(C4,1),IF(AND(WEEKDAY(C4,1)=6,WEEKDAY(D4,1)=7,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),NR_MFEveEnd-MOD(D4,1),IF(AND(WEEKDAY(C4,1)=1,WEEKDAY(D4,1)=2,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),MOD(D4,1)-NR_MFEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,WEEKDAY(D4,1)<> 1,WEEKDAY(D4,1)<> 7,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),(NR_MFEveEnd-MOD(C4,1))+(MOD(D4,1)-NR_MFEveStart),0))))))))

The formula for hours on Sat afternoon/evening is shorter, checking for hours worked which fall between 14:00 and 24:00 on Saturday:

=IF(OR(C4="",D4=""),"",IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)< NR_SatEveStart,MOD(D4,1)> NR_SatEveStart,MOD(D4,1)<=NR_SatEveEnd),MOD(D4,1)-NR_SatEveStart,IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)>=NR_SatEveStart,MOD(D4,1)<=NR_SatEveEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)< NR_SatEveStart),NR_SatEveEnd-NR_SatEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)>=NR_SatEveStart),NR_SatEveEnd-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=6,MOD(D4,1)> NR_SatEveStart),MOD(D4,1)-NR_SatEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)> NR_SatEveStart),NR_SatEveEnd-MOD(C4,1),0)))))))

The formula for night hours does the same sort of thing, checking for hours worked between 00:00 and 06:00, though it doesn't need to check for the day of the week, as this applies every day::

=IF(OR(C4="",D4=""),"",IF(AND(WEEKDAY(C4)=WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)<=NR_NightEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)=WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)> NR_NightEnd),NR_NightEnd-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)< NR_NightEnd),(NR_NightEnd-MOD(C4,1))+(MOD(D4,1)-NR_NightStart),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)>=NR_NightEnd,MOD(D4,1)<=NR_NightEnd),MOD(D4,1)-NR_NightStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)> NR_NightEnd,MOD(D4,1)>=NR_NightEnd),NR_NightEnd-NR_NightStart,0))))))

The Sunday hours formula is a lot shorter, just checking for hours worked on Sundays:

=IF(OR(C4="",D4=""),"",IF(AND(WEEKDAY(C4,1)=1,WEEKDAY(D4,1)=1),E4,IF(WEEKDAY(C4,1)=1,1-MOD(C4,1),IF(WEEKDAY(D4,1)=1,MOD(D4,1),0))))

The amount of pay for each period is then calculated by converting the time to decimal and multiplying by the rate of pay:

=(E4*24)*NR_BasePay

=(F4*24)*NR_MFEvePay

=(G4*24)*NR_SatEvePay

=(H4*24)*NR_NightPay

=(I4*24)*NR_SunPay

The total pay is then just a sum of the above:

=SUM(J4:N4)

Second sheet: AS2, which shows the pay for each period and the total pay - the hours worked in each period aren't shown - the formula calculating the pay for each period has the calculation of hours embedded in it. Essentially, it takes the formulae from sheet AS1 listed above for time-worked and combines those with the formulae for amount of pay.

Base pay stays much the same:

=(D4-C4)*24*NR_BasePay

Mon-Fri evening pay:

=(IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)< NR_MFEveStart,MOD(D4,1)> NR_MFEveStart,MOD(D4,1)<=NR_MFEveEnd),MOD(D4,1)-NR_MFEveStart,IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)>=NR_MFEveStart,MOD(D4,1)<=NR_MFEveEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)< NR_MFEveStart,MOD(D4,1)< NR_MFEveStart),NR_MFEveEnd-NR_MFEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)>=NR_MFEveStart,MOD(D4,1)<=NR_MFEveStart),NR_MFEveEnd-MOD(C4,1),IF(AND(WEEKDAY(C4,1)=6,WEEKDAY(D4,1)=7,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),NR_MFEveEnd-MOD(D4,1),IF(AND(WEEKDAY(C4,1)=1,WEEKDAY(D4,1)=2,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),MOD(D4,1)-NR_MFEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)< > 7,WEEKDAY(D4,1)<> 1,WEEKDAY(D4,1)<> 7,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),(NR_MFEveEnd-MOD(C4,1))+(MOD(D4,1)-NR_MFEveStart),0))))))))*24*NR_MFEvePay

Sat afternoon/evening pay:

=(IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)< NR_SatEveStart,MOD(D4,1)> NR_SatEveStart,MOD(D4,1)<=NR_SatEveEnd),MOD(D4,1)-NR_SatEveStart,IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)>=NR_SatEveStart,MOD(D4,1)<=NR_SatEveEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)< NR_SatEveStart),NR_SatEveEnd-NR_SatEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)>=NR_SatEveStart),NR_SatEveEnd-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=6,MOD(D4,1)> NR_SatEveStart),MOD(D4,1)-NR_SatEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)> NR_SatEveStart),NR_SatEveEnd-MOD(C4,1),0)))))))*24*NR_SatEvePay

Night pay:

=(IF(AND(WEEKDAY(C4)=WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)<=NR_NightEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)=WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)> NR_NightEnd),NR_NightEnd-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)< NR_NightEnd),(NR_NightEnd-MOD(C4,1))+(MOD(D4,1)-NR_NightStart),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)>=NR_NightEnd,MOD(D4,1)<=NR_NightEnd),MOD(D4,1)-NR_NightStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)> NR_NightEnd,MOD(D4,1)>=NR_NightEnd),NR_NightEnd-NR_NightStart,0))))))*24*NR_NightPay

Sunday pay:

=IF(AND(WEEKDAY(C4,1)=1,WEEKDAY(D4,1)=1),E4,IF(WEEKDAY(C4,1)=1,1-MOD(C4,1),IF(WEEKDAY(D4,1)=1,MOD(D4,1),0)))*24*NR_SunPay

Total pay is again a sum of the other columns:

=SUM(F4:J4)

Third sheet: AS3, which shows only the total pay due - the hours worked and pay for each period aren't shown - the final formula does all of it in one:

=IF(OR(C4="",D4=""),"",(D4-C4)*24*NR_BasePay + (IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)< NR_MFEveStart,MOD(D4,1)> NR_MFEveStart,MOD(D4,1)<=NR_MFEveEnd),MOD(D4,1)-NR_MFEveStart,IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)>=NR_MFEveStart,MOD(D4,1)<=NR_MFEveEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)< NR_MFEveStart,MOD(D4,1)< NR_MFEveStart),NR_MFEveEnd-NR_MFEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,MOD(C4,1)>=NR_MFEveStart,MOD(D4,1)<=NR_MFEveStart),NR_MFEveEnd-MOD(C4,1),IF(AND(WEEKDAY(C4,1)=6,WEEKDAY(D4,1)=7,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),NR_MFEveEnd-MOD(D4,1),IF(AND(WEEKDAY(C4,1)=1,WEEKDAY(D4,1)=2,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),MOD(D4,1)-NR_MFEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)<> 1,WEEKDAY(C4,1)<> 7,WEEKDAY(D4,1)<> 1,WEEKDAY(D4,1)<> 7,MOD(C4,1)> NR_MFEveStart,MOD(D4,1)> NR_MFEveStart),(NR_MFEveEnd-MOD(C4,1))+(MOD(D4,1)-NR_MFEveStart),0))))))))*24*NR_MFEvePay + (IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)< NR_SatEveStart,MOD(D4,1)> NR_SatEveStart,MOD(D4,1)<=NR_SatEveEnd),MOD(D4,1)-NR_SatEveStart,IF(AND(WEEKDAY(C4)=WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)>=NR_SatEveStart,MOD(D4,1)<=NR_SatEveEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)< NR_SatEveStart),NR_SatEveEnd-NR_SatEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)>=NR_SatEveStart),NR_SatEveEnd-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=6,MOD(D4,1)> NR_SatEveStart),MOD(D4,1)-NR_SatEveStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),WEEKDAY(C4,1)=7,MOD(C4,1)> NR_SatEveStart),NR_SatEveEnd-MOD(C4,1),0)))))))*24*NR_SatEvePay + (IF(AND(WEEKDAY(C4)=WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)<=NR_NightEnd),MOD(D4,1)-MOD(C4,1),IF(AND(WEEKDAY(C4)=WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)> NR_NightEnd),NR_NightEnd-MOD(C4,1),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)< NR_NightEnd,MOD(D4,1)< NR_NightEnd),(NR_NightEnd-MOD(C4,1))+(MOD(D4,1)-NR_NightStart),IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)>=NR_NightEnd,MOD(D4,1)<=NR_NightEnd),MOD(D4,1)-NR_NightStart,IF(AND(WEEKDAY(C4)<> WEEKDAY(D4),MOD(C4,1)> NR_NightEnd,MOD(D4,1)>=NR_NightEnd),NR_NightEnd-NR_NightStart,0))))))*24*NR_NightPay + IF(AND(WEEKDAY(C4,1)=1,WEEKDAY(D4,1)=1),E4,IF(WEEKDAY(C4,1)=1,1-MOD(C4,1),IF(WEEKDAY(D4,1)=1,MOD(D4,1),0)))*24*NR_SunPay)

Last Notes


All the formulae above (on all the sheets) should work for shifts up to 24 hours. They may work for longer periods, but I've only tested up to 24 hours.


Finally, please note that all of the formulae above use commas (,) as separators. Your settings may use semi-colons (;) instead. Obviously, the formulae are also in English. However, if you look at the attached file in Danish, then Excel will automatically convert the formulae - both words and commas.

Sorry it's taken so long, but I hope this does what you need. If you find something not working, or you'd like to change something but don't know how, then just let me know.