Excel/CSV Time Zone Change Formula? 3

[lkerr]

I have a CSV, which I open in Excel 2007, containing a list of events, with their start times and end times. However, the start time and end times are in Eastern Time and I'm Central time. Before I can upload this list into Outlook, I need to change all these times to be minus 1 hour.

An example:

Event Start Finish
Meeting A 12:00:00 PM 1:00:00 PM
Meeting B 2:00:00 PM 2:30:00 PM
Meeting C 11:30:00 AM 12:30:00 PM

I'm at a loss of even where to begin with this one. Anyone?

 

[xlhelp]

Subtract 0.04166666 from the original value.

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

When you understand that TIME values are in units of DAYS, then just convert 1 hour to days and subtract (1/24)

Skip,

_[red][/red]

 

[lkerr]

Thanks! This was just a "duh" moment for me, I think. Occasionally I need hit upside the head with the obvious.

(Why is the hard stuff so much easier? I can write vlookups for hours without problems. Heh.)

 

[AnotherHiggins]

A few other ways that are (IMHO) a bit more intuitive than "-0.04166666":

=A1-TIMEVALUE("1:00:00")[tab][gray](for that matter, TIMEVALUE("1:") will work, too)[/gray]
=A1-TIME(1,,)
=A1-(1/24)[tab][gray](1/24 of a day is 1 hour)[/gray]



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

John,

The Kama Sutra of Time! Let me count the ways... ;-)

Skip,

_[red][/red]

 

[lkerr]

FYI, I did end up using =d2-(1/24) ... kinda a combo of the original help-posts to end up with the third.