• ## Time calculation

Author
Topic
#2425272

=IF(V405=””,””,W405-V405)

V & W are sleep start and sleep wake times.  The formula works fine as long as the times are on the same day, but fails if the times cross the day boundary.

I did some searches.  Saw something that seemed to say that I needed to change the FORMAT I was using for this to work?  But doing so, didn’t seem to solve the problem.

This shouldn’t be rocket science!

Author
Replies
• #2425278

I tried to add a .pdf screen capture but it doesn’t seem to take.

• #2425282

A quick online search suggested it’s not something Excel does without some form of add-in (usually premium…).

I dare say there would be a VBA formula to address the situation, if one was to get knee-deep into it 😉
Good luck!

• #2425287

Use format [h]:mm. For example

A1 = ’23Feb22 01:23′

A2 =  ’22Feb22 23:59′

A2-A1 formatted as [h]:mm = 1.24

• #2425351

Use format [h]:mm. For example

A1 = ’23Feb22 01:23′

A2 =  ’22Feb22 23:59′

A2-A1 formatted as [h]:mm = 1.24

That did not work for me.  Let me try and post the screenshot image yet again.  This time I converted it to JPEG instead of PDF.

• #2425366

I could make that work…

In cell A1: “23/02/2022 1:23:00 a.m.” (cell format H:mm)
In cell A2: “22/02/2022 10:00:00 p.m.” (ditto cell format)
In cell B2: “=+A1-A2” Result: 3:23 (ditto cell format)

• #2425362

OK, I misunderstood what you were trying to do.

If Column A always contains an earlier time and Column B always contains a later time you can do the following:

If the ‘time’ columns include dates (whether they’re shown or not) a simply subtracting the earlier time from the later time will work.

If there’s any chance the time difference will be more than 24hrs the ‘result’ cell format should be custom set to ‘[h]:mm’. Note the [] must be included in the format – just type that string into the ‘type’ column show in your post.

Examples below and attached.

Hopefully that does what you want.

• #2425403

I’m not quite clear on what you are doing here.  The sleep columns are time only, no dates, hidden or otherwise.

The 2nd column of the pair will always be further along in time than the first column but, as in this example, may cross the day boundary.  This does not happen very often as I usually go to bed after midnight.

I was hoping  for a general solution that would apply to all the columns and rows in what is a yearly spreadsheet that contains 4 sleep column pairs.

• #2425406

This is a general purpose solution. As you’ve said it will only contain times, all you need to do is copy the formula in C3 and make sure it is pointing to the correct input values. For example, if column A contains the ‘first’ time and column B contains the ‘second’ time put the formula into column C and make sure the row numbers are the same. You can also copy that formula to the other 3 column pairs then copy or fill down all rows.

If you want, upload the spreadsheet, or a sample of it and I’ll put it in for you.

###### 1 user thanked author for this post.
• #2425809

OK, been busy.  Will play with it next couple of days.  Thanks!

• #2428168

I tried testing this today.  Unfortunately, it did not work.

Attached is a screenshot.  Any idea what I might be doing wrong?

This is the formula you gave me that I am using in these test cells:

=IF(V4<W4,TIMEVALUE(“23:59”)-V4+TIMEVALUE(“00:01”)+W4,W4-V4)

• #2428188

Ignore previous post – I wasn’t logged in.

The test should be later time < earlier time. In my case B3<A3 or in your case W4<V4. Full formula should be:

=IF(W4<V4,TIMEVALUE(“23:59”)-V4+TIMEVALUE(“00:01”)+W4,W4-V4)

See how that goes.

• #2428261

OK, that seems to work now.

BTW: I had to change your quotes.  Excel was generating an error and I couldn’t figure out why.  Retyping the quotes fixed the problem.  Thanks again.

• #2428273

The forum software mucks up quotes. The safest way to write quotes in the forum is in a code box on the Text tab.

`=IF(W4<V4,TIMEVALUE("23:59")-V4+TIMEVALUE("00:01")+W4,W4-V4)`

cheers, Paul