Date Conversions

[Graeme]

Hello

I'm trying to put a spreadsheet together that will allow me to enter a date and time which is GMT and convert it to MST (GMT -7 hours). So I can enter mm/dd/yyyy hh:mm AM/PM in GMT and get the MST equivalent.

I'm struggling with two aspects

1. If I format a cell to mm/dd/yyyy it still displays in my regional default dd/mm/yyyy.

2. I tried to separate the hh:mm from the mm/dd/yyy to subtract the 7 hours with the idea of concatenating the two after in another cell. But this would then not see the date change if the time change is over midnight. And the result of subtracting 7 from hh:mm is a row of #####s!

I can see this looks like having to be coded solution rather than simple cell formulae but that will probably be above my pay grade!

Regards

Graeme

[HansV]

Keep in mind that dates and times are stored as numbers in Excel.
Dates are stored as whole numbers: the number of days since the beginning of the 20th century.
Times are stored as fractions of a day: 6:00 AM is stored as 0.25, and 18:00 / 6:00 PM is stored as 0.75.

The way they are displayed is determined by the number format - a date can be displayed as 15/12/2020 or as 12/15/2020 or as 2020-12-15 or as 15 December, the underlying number remains the same.

You always have to ENTER dates in your local date format. Since you're in the UK, I assume that's dd/mm/yyyy or similar.
You can use the number format to DISPLAY it differently if you wish.

You don't have to separate the date and time.
Let's say you enter a date and time in A2.
Enter the following formula in B2:

=A2-TIME(7,0,0)

and apply the desired date/time format.

[HansV]

And if you need the converted date and time in text format, use

=TEXT(A2-TIME(7,0,0),"mm/dd/yyyy hh:mm AM/PM")

[Graeme]

Perfect!

Thanks

And I've added a BST column too to check my mental arithmetic when the clocks go forward in the spring. MST has no daylight saving time. Must be very useful for the Arizonian astronomers!

Regards

Graeme