Going round in circles and finally admitting defeat. Photo of the data set I am currently working with – this relates to hours worked for staff. 

What I need to do in the unsocial hours column (Col O) is account for any hours worked between midnight (23:59) and 6am (06:00). E.g. for the first Row O2 should read 02:20 for 2hrs 20mins. 

First thing i’m stuck on is i have used =TEXT(H2, “HH:MM:SS”) in columns M and N to convert the date/time in H and I into time only. However – this isn’t overwriting the original date in the underlying data, so every combination of IF statements that I try is giving false results. 

I’m sure there is a very simple and straightforward answer, my brain is just fried. Thanks very much. 

​Going round in circles and finally admitting defeat. Photo of the data set I am currently working with – this relates to hours worked for staff. What I need to do in the unsocial hours column (Col O) is account for any hours worked between midnight (23:59) and 6am (06:00). E.g. for the first Row O2 should read 02:20 for 2hrs 20mins. First thing i’m stuck on is i have used =TEXT(H2, “HH:MM:SS”) in columns M and N to convert the date/time in H and I into time only. However – this isn’t overwriting the original date in the underlying data, so every combination of IF statements that I try is giving false results.   I’m sure there is a very simple and straightforward answer, my brain is just fried. Thanks very much.     Read More

​