time stamps...

[dan_l]

I can't believe I'm having this trouble!

I've got a series of time stamps. They're just time. IE: 2:35:00 PM. There's a date in the output, but it's wrong. If I change the formatting it's like 1/1/2001 2:35:00 PM.

I need to do two things:
1) With a formula, change the date to the right day (IE: 9/16/2015 2:35:00 PM)
2) Subtract 7 hours from these times, even where the subtraction will put me into the previous day.

I can't think of a good way to do this. Any thoughts?

 

[chirayu]

Sample file please. Unsure which time format you're using i.e. MMDDYY/ DDMMYY/ YYMMDD/ YYDDMM

 

[NARAYANK991]

Hi Dan ,

You say :

They're just time.

but the example you have given where the date component is 1/1/2001 shows that it is not a time component alone.

Enter the time value of 14:35:00 or 2:35:00 PM in a cell , change its format to include a date component and see what it displays.

Be that as it may , isolating the time component from any decimal value which represents a date + time component is a simple matter of using the formula :

=DateTimeValue - INT(DateTimeValue)

or

=MOD(DateTimeValue , 1)

However , the result of the above formulae will not equal an entered time such as 14:35:00 or 2:35:00 PM ; there is a difference between the two after the 10th decimal place !

Changing the above result to include today's date is a matter of adding TODAY() to the above formula.

Subtracting 7 hours would be done by :

- "07:00:00"

The full formula would be :

=MOD(DateTimeValue , 1) + TODAY() - "07:00:00"

Narayan

 

[Chihiro]

To add to Narayan's explanation above, you can express time in fraction as well.

1 hour = 1/24

To circumvent issue of mismatch after 10th decimal place, you can use ROUND().

For example.

=MOD("1/1/205 1:00 PM",1)=13/24
Will return False.

=ROUND(MOD("1/1/2015 1:00 PM",1),10)=ROUND(13/24,10)
Will return True.