I am having an issue calculating time, I need to calculate the time between the start of the shift at 12:30am and various release times. However, sometimes the in time is prior to the start of the shift so I need the formula to look at the in time and decide if it is before or after the 12:30am start.
In English this is what I came up with:
If the in time (C) is less than or equal to the shift start time of 12:30am (B) then calculate the difference between 12:30am (B) & the release time (A). If the in time (c) is greater than the shift start time of 12:30am (b) then find the difference between the in time (c) & release time (a).
Formula I tried to work was:
=IF(C<=B, A-B, B-C)
This somewhat worked, it gave me trouble calculating from the day prior i.e 11pm to 5am. Then I would manipulate the formula to include two if clauses & I still couldn't get it to work.
example of data being inputted
In @ 12:30 am, Out 9:00 am
In @ 12:45 am, Out 6:00 am
In @ 10:00 pm, Out at 4:00 am
Any help would be appreciated!!!!
In English this is what I came up with:
If the in time (C) is less than or equal to the shift start time of 12:30am (B) then calculate the difference between 12:30am (B) & the release time (A). If the in time (c) is greater than the shift start time of 12:30am (b) then find the difference between the in time (c) & release time (a).
Formula I tried to work was:
=IF(C<=B, A-B, B-C)
This somewhat worked, it gave me trouble calculating from the day prior i.e 11pm to 5am. Then I would manipulate the formula to include two if clauses & I still couldn't get it to work.
example of data being inputted
In @ 12:30 am, Out 9:00 am
In @ 12:45 am, Out 6:00 am
In @ 10:00 pm, Out at 4:00 am
Any help would be appreciated!!!!