Really complicated timesheet

[shakaka]

I'm creating a spreadsheet to help to determine if a shift is actually a night shift or a day shift.

The rule I have been given is as follows:

"Night Shift Hours are deemed to start at 22:30 and end at 05:45 the following morning.
Night Shift working is defined as a shift where 50% of the hours are worked between 22:30 and end at 05:45 the following morning."

I need to find a formula to state if the shift is night or day shift.

Cell A1 = Start Time
Cell B1 = End Time
Cell C1 = Night Shift / Day Shift

Does anyone know of an IF statement that could help me figure this out.

Thanks

[AliGW]

Yes, this can be done.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[shakaka]

I've uploaded a really trimmed down version of my spreadsheet.

You'll see the following:
column C has the start time
column D has the end time
column E is where a result of Y or N should be.

Hopefully this helps.

Thanks again

[AliGW]

Could you please add some expected results and then upload the workbook again? Thanks.

[shakaka]

I think I have figured it out.

Although if it is correct might have me raising some questions with the company I work for.

I've attached the sheet to show my workings and if anyone has an easier way of doing what I've done

[AliGW]

So are you still looking for a formula to use to work out the hours that you have entered manually?

[shakaka]

Nah it was to work out if it was a night shift,
so column E.

I've shown columns BE, BF and BG to show how I got the the answer shown in column E.

If there is a formula where I wouldn't need columns BE,BF and BG that would be amazing as on my main spreadsheet there are over 37,000 rows.
Obviously that's an additional 111,000 cells of data.

[AliGW]

So you DO still want a formula that will work out what you have done manually, but preferably as one composite formula in column E - yes? I am afraid I have run out of time for this evening, but there will be others around who can help, if that is what you require.