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

Welcome to the forum.
Please upload a sample file. Don't include any confidential information, but do show the data you have to input and the result(s) you want - give an example calculated manually if possible. We need to know exactly what criteria you need to include in the calculation - so far you've mentioned a base salary (presumably per hour), Sunday bonus and night bonus - are there any others, for example? Are these bonuses fixed amounts or percentages? Once we can see this, we can try to work out a formula for you.

To upload a file, click 'Go Advanced' then scroll down to 'Manage Attachments' and upload from there.

Let me start by saying thank you so much for even looking at this.

I have made up some dates next week which are representative for a typical week.
On Sunday the 15th for example I’ll start at 8:30 p.m. and finish Saturday morning at 7:15 a.m.
That means the following, three and a half hours will be Saturday, so I’ll will be getting “Saturday night work bonus” which is from 2 p.m. until midnight.

from the start of Sunday morning until the end of my shift I will receive "Sunday work bonus", and additionally I will also receive “nights bonus” for 6 hours that day, because it is applicable from midnight until 6 a.m. any night of the week.

So on a Saturday night shift i'll get pay and 3 different bonus pays, and on a Thursday i work the same amount of hours but get €30 less because it's midweek and midday.

I don't know if it is possible to build a formula that can calculate this.

I am really grateful that you are looking at this, thanks.

It's certainly possible, but the formulae will be a bit complicated as they'll need to check various combinations of day of week, start time, end time and so on. A single formula will probably end up being huge and difficult to understand (or amend if necessary), so it might be best to have a few helper columns to do intermediate calculations which can then be combined - they can be hidden so you just see the final results.
I'll look at this over the next couple of days and get back to you - sorry it won't be quicker but I've got a few other things going on just now (family and work).

Edit:
A question - is there a maximum number of hours you will work at a time?
What I'm wondering is, could you start work at 05:30 one morning and finish at 00:30 the next day, for example, or start at 23:00 on Fri evening and work to 15:00 on Sat?
If this is possible, then I need to make sure the formulae account for all possible combinations across your various bonus periods. If you would never work more than 12 hours, or 15 hours, or whatever, then I can disregard those combinations.
Hope that makes sense!

Just to let you know I haven't forgotten about this. Unfortunately I've been very busy the last couple of weeks (helping my wife's parents move house, among other things) but I'm hoping to have this finished in the next couple of days. Currently it involves a few helper columns which I'm hoping to reduce a bit, though there will still probably be a few, just to make it understandable!

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:

Please Login or Register  to view this content.

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:

Formula:

Please Login or Register  to view this content.

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:

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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::

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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:

Formula:

Please Login or Register  to view this content.

Mon-Fri evening pay:

Formula:

Please Login or Register  to view this content.

Sat afternoon/evening pay:

Formula:

Please Login or Register  to view this content.

Night pay:

Formula:

Please Login or Register  to view this content.

Sunday pay:

Formula:

Please Login or Register  to view this content.

Total pay is again a sum of the other columns:

Formula:

Please Login or Register  to view this content.

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:

Formula:

Please Login or Register  to view this content.

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.