Converting a number into time

[datadummy]

I have a report that is giving me times in a general format where instead of giving me 12:18 AM it appears as 18 and so on and I want to subtract one column from another, but my issue is when doing so I am not getting a time rather just a general number. For example cell C2 has 18 (12:18AM) and cell J2 has 504 (5:04AM) and I want subtract J from C to get 4:46 or even 446 and am unable to get the calculations right due to format as general rather than a time. Any thoughts on achieving my desired outcome?

 

[Logit]

.
The cells containing the times ... Right click the cells , select FORMAT / TIME (or CUSTOM) and select the preferred time format display.

If the times will start prior to midnight and end after midnight, be certain to include the DATE in the FORMAT selection.

 

[datadummy]

That didn't seem to work I took my example of 18 (12:18 AM) and when I changed the format regardless of which format I selected it gave me either 12:00:00 or 0:00 and ended up making the date 1/18/1900. I have no date reference so I don't know that utilizing a format with date is appropriate.

 

[Logit]

.
Can you post your workbook to a cloud site like DROPBOX, etc.

 

[Rick Rothstein]

What do you ultimately want to do... correct the display of the 18 and 504 type values (you will need a macro for that) so that the =J2-C2 formula works OR leave those value and have a formula that does the subtraction correctly? If the former, let us know and someone will write the macro for you. As for the latter, this formula will produce the correct time value...

=TEXT(J2,"0\:00")-TEXT(C2,"0\:00")

however, you will need to format the cell with the time format you want in order to see it displayed correctly.

 

[datadummy]

That worked for the most part except for when I had a time that was prior to 12 midnight for example 1725 (5:25 PM) and the following time the following morning is 259 (2:59 AM) and it returns the value of 14:26 rather than 9 hours 34 minutes. Here is what I added to the formula =IF(J13>C13,(TEXT(J13,"0\:00")-(TEXT(C13,"0\:00"))),(TEXT(C13,"0\:00")-(TEXT(J13,"0\:00"))))
If it is possible to make a macro that achieves my desired goal with the current formats?

 

[Rick Rothstein]

That worked for the most part except for when I had a time that was prior to 12 midnight for example 1725 (5:25 PM) and the following time the following morning is 259 (2:59 AM) and it returns the value of 14:26 rather than 9 hours 34 minutes. Here is what I added to the formula =IF(J13>C13,(TEXT(J13,"0\:00")-(TEXT(C13,"0\:00"))),(TEXT(C13,"0\:00")-(TEXT(J13,"0\:00"))))

This would be a better formula to use...

=MOD(TEXT(J2,"0\:00")-TEXT(C2,"0\:00"),24)

If it is possible to make a macro that achieves my desired goal with the current formats?

You want a macro replace the (above) formula as opposed to changing the Column C and Column J values to real time values (you cannot format that change... it would need to be a physical change)?

 

[datadummy]

That formula did exactly what I was going for, thank you for your help on this!!