How to subtract 1 hour from a 24hour time in string format value?

Hi guys,

I have a lot of 24hour times - in string format - in column 'A', and I should subtract 1 hour. How should I do it?

So, this is how it looks like: A column is the base, and B column is the desired ones. And column A is in string format.

'A'	'B'
12:40	11:40
13:35	12:35
15:20	14:20
18:05	17:05

=text(b8,"hh:mm")-time(1,0,0)

Originally Posted by PFDave

With Time Function

Formula:

Please Login or Register  to view this content.

As Text Value

Formula:

Please Login or Register  to view this content.

Thank you!

Originally Posted by cbatrody

If the time in column A is in Text format, then try the following in B2:

=TIME(LEFT(A2,SEARCH(":",A2)-1)-1,RIGHT(A2,2),0)

OR,

If column A is in Time format, try the following in B2:

=TIME(HOUR(A2)-1,MINUTE(A2),0)

OR

=A2-1/24

Thank you, it is also working, thank you all, guys!

Originally Posted by shukla.ankur281190

b1

Formula:

Please Login or Register  to view this content.

Thanks, it also working, but needs to convert the cell into time format. Cheers!

Hi, PFDave,

This is the formula, which I need, because it reamins the outcome in TEXT format as well, but it isn't working if the time is 20:00, because it thinks, it is 00:00.
Where should I modify the formula?

Originally Posted by PFDave

As Text Value

Formula:

Please Login or Register  to view this content.

Thanks!

Thanks, I'll look up it.
I figured it out, but not with a nice solution: I copied another IF formula same as the 23 hour: if the hour is 20, then make it 19. But it is not so nice solution. Anyway, I'll try this now.

Unfortunately, it's not okay.

excel_subtract_hour.png

This is my not so nice solution:

Please Login or Register  to view this content.

But to copy another IF formula into the IF formula is not a nice solution...

Actually, I really don't understand, why it isn't working with the 20:00, and only with the 20:00.

Can you upload the example file in excel format instead of image please?

Originally Posted by cbatrody

Can you upload the example file in excel format instead of image please?

Here it is:

https://anonfile.com/i6JeH1beb3/time_substract.xlsx

Try the following in G1:

=TIME(LEFT(E1,SEARCH(":",E1)-1)-1,RIGHT(E1,2),0)

Please see the attached file.

Almost, but it's not working with eg. 00:30. But with the IF formula from the previous one, it will be good.
As you see, I like to learn, can you tell me, why it didn't work between 20:00 an 20:59?

Oh, no, it's not okay, because I need the result in text format as well.

The reason I need it in text format as well (so, both the starting point is in string format, then I should subtract 1 hour, and then the result should be in string as well), because then I need the result, and "CONCATENATE" it with other strings as well..

Try the following in H1:

=IF(LEFT(E1,2)="00","23"&RIGHT(E1,3),TEXT(LEFT(E1,2)-1,"00")&RIGHT(E1,3))

Yesss! )