Changing negative time to negative decimal

[iheartexcel2024]

Hi everyone,

I am trying to work out the overtime in decimal and in this case I would like to change the negative overtime into negative decimal value but some are showing error #NUM!.

Does anyone know how to fix this?

I have enabled 1904 date system.

 

[Phuoc]

Try this in H5:

=G5*24

 

[iheartexcel2024]

Try this in H5:

=G5*24

Oh it works like a charm Phuoc, thank you sooo much! But may I know why we have to multiply by 24?

 

[Phuoc]

Because there are 24 hours in a day

 

[iheartexcel2024]

Because there are 24 hours in a day

Yes I understand there are 24 hours in a day but I just dont know understand how time multiply by 24 can instantly give decimal point.

For example overtime 0:24 (24 minutes) * 24 = 0.40

Previously I was using 24/60 to get 0.40

 

[Alex Blakenburg]

Excel stores dates as the number of days since the base date. Normally from 1/1/1900 in your case 1/1/1904.
It then stores time as a decimal fraction of a day. To convert that decimal fraction of a day to hours you need to multiply it by the hour equivalent of 1 day being 24 hours.

Further reading: https://www.cpearson.com/excel/datetime.htm

 

[Scott Huish]

@Alex Blakenburg , that link shows as a security risk in Firefox although I had no issues ignoring the warning.

@iheartexcel2024 Following up on what Alex said, 12:00 PM in Excel is stored as .5 (half a day), so multiplying by 24 would give you 12 hours

 

[MARK858]

@Alex Blakenburg , that link shows as a security risk in Firefox although I had no issues ignoring the warning.

Does for me with my software but it is probably because it isn't actually Https it is still the old straight www