|
There are isolated problems with current patches, but they are well-known and documented on this site. |
=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!
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!
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.
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)
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.
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.
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)
I had to change your quotes
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
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
