GMT & UTC time ?

peterwillmo · Dec 19, 2017

I have a spreadsheet with 4000+ lines of data and i wish to display uk gmt time in cell a1 and utc in cell b1. Could someone please give me the correct formulas for each cell?

MARK858 · Dec 19, 2017

Might help if you posted what your data looks like, what it is, where the time is being extracted from and what column it is in.

bensmith1987 · Dec 19, 2017

peterwillmo said:
I have a spreadsheet with 4000+ lines of data and i wish to display uk gmt time in cell a1 and utc in cell b1. Could someone please give me the correct formulas for each cell?

GMT & UTC are currently the same although worth noting GMT is a Time Zone & UTC being a Time Standard.
Unless what you are trying to achieve is something else?

Can you provide a sample of the data along with how you want it to look in both columns?

Thanks
Ben

peterwillmo · Dec 19, 2017

bensmith1987 said:
GMT & UTC are currently the same although worth noting GMT is a Time Zone & UTC being a Time Standard.
Unless what you are trying to achieve is something else?

Can you provide a sample of the data along with how you want it to look in both columns?

Thanks
Ben

Thanks for the reply Ben.

Simply 12:36 in A1 and 12:36 in B1 but to encompass changes when we change to Summer time from 26 March to 29 October if this is possible!

bensmith1987 · Dec 19, 2017

Ok so below is the simplest solution which works provided the date ranges within your spreadsheet are confined to within the year (ie: 2018 for below). You can simply change the dates in the formula depending on when BST starts & ends that year. If you need multiple years a more complex solution may be needed involving VLOOKUPS to a seperate tab or nested formulae.

Three Columns:
UK Time: =IF(B2="GMT",C2,C2+0.0416666666666666)
Time Zone: =IF(AND(TODAY()>DATEVALUE("24/03/2017"),TODAY()<DATEVALUE("28/10/2018")),"BST","GMT")
UTC: The time in raw data format - this will not change

Two Columns:
If you only want 1 column then use the below in UK Time:
=IF(AND(TODAY()>DATEVALUE("24/03/2018"),TODAY()<DATEVALUE("28/10/2018")),C2+0.0416666666666666,C2)

End Result:

<colgroup><col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2872;width:61pt" width="81"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 78"]UK Time[/TD]
[TD="class: xl65, width: 81"]Time Zone[/TD]
[TD="class: xl65, width: 64"]UTC[/TD]

[TD="class: xl66"]12:36[/TD]
[TD="class: xl66"]GMT[/TD]
[TD="class: xl66"]12:36[/TD]

[TD="class: xl66"]13:36[/TD]
[TD="class: xl66"]UTC[/TD]
[TD="class: xl66"]12:36
[/TD]

</tbody>

(2nd data row overridden manually to show output)

If however you want to apply the formula based on a date included within the table (example column D) then use the below formula in Col B (Time Zone) or use above steps to integrate into Col A:
=IF(AND(D2>DATEVALUE("24/03/2018"),D2<DATEVALUE("28/10/2018")),"BST","GMT")

bensmith1987 · Dec 19, 2017

bensmith1987 said:
Ok so below is the simplest solution which works provided the date ranges within your spreadsheet are confined to within the year (ie: 2018 for below). You can simply change the dates in the formula depending on when BST starts & ends that year. If you need multiple years a more complex solution may be needed involving VLOOKUPS to a seperate tab or nested formulae.

Three Columns:
UK Time:

Code:

=IF(B2="GMT",C2,C2+0.0416666666666666)

Time Zone:

Code:

=IF(AND(TODAY()>DATEVALUE("24/03/2017"),TODAY()<DATEVALUE("28/10/2018")),"BST","GMT")<datevalue("28 10="" 2018")),"bst","gmt")

UTC: The time in raw data format - this will not change

Two Columns:
If you only want 1 column then use the below in UK Time:

Code:

=IF(AND(TODAY()>DATEVALUE("24/03/2018"),TODAY()<DATEVALUE("28/10/2018")),C2+0.0416666666666666,C2)

<datevalue("28 10="" 2018")),c2+0.0416666666666666,c2)

End Result:

<tbody>
[TD="class: xl65"]UK Time[/TD]
[TD="class: xl65"]Time Zone[/TD]
[TD="class: xl65, width: 64"]UTC
[/TD]

[TD="class: xl66"]12:36[/TD]
[TD="class: xl66"]GMT[/TD]
[TD="class: xl66"]12:36[/TD]

[TD="class: xl66"]13:36[/TD]
[TD="class: xl66"]UTC[/TD]
[TD="class: xl66"]12:36[/TD]

</tbody>
(2nd data row overridden manually to show output)

If however you want to apply the formula based on a date included within the table (example column D) then use the below formula in Col B (Time Zone) or use above steps to integrate into Col A:

Code:

=IF(AND(D2>DATEVALUE("24/03/2018"),D2<DATEVALUE("28/10/2018")),"BST","GMT")<datevalue("28 10="" 2018")),"bst","gmt")[="" quote] </datevalue("28></datevalue("28></datevalue("28>

Not sure what happened there - above now fixed & examples in excel file (link below):
[FONT=&quot]https://we.tl/cYKMJ6wDBN [/FONT]

bensmith1987 · Dec 19, 2017

bensmith1987 said:
bensmith1987 said:

Ok so below is the simplest solution which works provided the date ranges within your spreadsheet are confined to within the year (ie: 2018 for below). You can simply change the dates in the formula depending on when BST starts & ends that year. If you need multiple years a more complex solution may be needed involving VLOOKUPS to a seperate tab or nested formulae.

Three Columns:
UK Time:

Code:

=IF(B2="GMT",C2,C2+0.0416666666666666)

Time Zone:

Code:

=IF(AND(TODAY()>DATEVALUE("24/03/2017"),TODAY()<datevalue("28 10="" 2018")),"bst","gmt")<datevalue("28="" 2018")),"bst","gmt")

UTC: The time in raw data format - this will not change

Two Columns:
If you only want 1 column then use the below in UK Time:

Code:

=IF(AND(TODAY()>DATEVALUE("24/03/2018"),TODAY()<datevalue("28 10="" 2018")),c2+0.0416666666666666,c2)[="" code] <datevalue("28 10="" 2018")),c2+0.0416666666666666,c2) [B]End Result:[/B] [TABLE] <tbody>[TR] [TD="class: xl65"]UK Time[/TD] [TD="class: xl65"]Time Zone[/TD] [TD="class: xl65"]UTC[/TD] [/TR] [TR] [TD="class: xl66"]12:36[/TD] [TD="class: xl66"]GMT[/TD] [TD="class: xl66"]12:36[/TD] [/TR] [TR] [TD="class: xl66"]13:36[/TD] [TD="class: xl66"]UTC[/TD] [TD="class: xl66"]12:36[/TD] [/TR] </tbody>[/TABLE] (2nd data row overridden manually to show output) If however you want to apply the formula based on a date included within the table (example column D) then use the below formula in Col B (Time Zone) or use above steps to integrate into Col A: [CODE]=IF(AND(D2>DATEVALUE("24/03/2018"),D2<datevalue("28 10="" 2018")),"bst","gmt")<datevalue("28="" 2018")),"bst","gmt")[="" quote] </datevalue("28></datevalue("28></datevalue("28>

Not sure what happened there - above now fixed & examples in excel file (link below):
https://we.tl/cYKMJ6wDBN

Click to expand...

As you can guess im a tad new to this forum and can't seem to edit my posts -must be an issue with the fomulae i'm typing. See examples in linked excel file and let me know if any problems!

Cheers
Ben
</datevalue("28>

MARK858 · Dec 19, 2017

Ben, put a space after the < symbol in your formulas. There is also something strange with the window, are you using just the reply button?

bensmith1987 · Dec 19, 2017

bensmith1987 said:
bensmith1987 said:

Ok so below is the simplest solution which works provided the date ranges within your spreadsheet are confined to within the year (ie: 2018 for below). You can simply change the dates in the formula depending on when BST starts & ends that year. If you need multiple years a more complex solution may be needed involving VLOOKUPS to a seperate tab or nested formulae.

Three Columns:
UK Time:

Code:

=IF(B2="GMT",C2,C2+0.0416666666666666)

Time Zone:

Code:

=IF(AND(TODAY()>DATEVALUE("24/03/2018"),TODAY()< DATEVALUE("28/10/2018")),"BST","GMT")<datevalue("28 10="" 2018")),"bst","gmt")<datevalue("28="" 2018")),"bst","gmt")

UTC: The time in raw data format - this will not change

Two Columns:
If you only want 1 column then use the below in UK Time:

Code:

=IF(AND(TODAY()>DATEVALUE("24/03/2018"),TODAY()< DATEVALUE("28/10/2018")),B2+0.0416666666666666,B2)

<datevalue("28 10="" 2018")),c2+0.0416666666666666,c2)[="" code]
<datevalue("28 10="" 2018")),c2+0.0416666666666666,c2)

End Result:

<tbody>
[TD="class: xl65"]UK Time[/TD]
[TD="class: xl65"]Time Zone[/TD]
[TD="class: xl65"]UTC[/TD]

[TD="class: xl66"]12:36[/TD]
[TD="class: xl66"]GMT[/TD]
[TD="class: xl66"]12:36[/TD]

[TD="class: xl66"]13:36[/TD]
[TD="class: xl66"]UTC[/TD]
[TD="class: xl66"]12:36[/TD]

</tbody>
(2nd data row overridden manually to show output)

If however you want to apply the formula based on a date included within the table (example column D) then use the below formula in Col B (Time Zone) or use above steps to integrate into Col A:

Code:

=IF(AND(D2>DATEVALUE("24/03/2018"),D2< DATEVALUE("28/10/2018")),"BST","GMT")</datevalue("28></datevalue("28>

Not sure what happened there - above now fixed & examples in excel file (link below):
https://we.tl/cYKMJ6wDBN

Click to expand...

Thanks MARK858
</datevalue("28>

GMT & UTC time ?

peterwillmo

New Member

Excel Facts

MARK858

MrExcel MVP

bensmith1987

New Member

peterwillmo

New Member

bensmith1987

New Member

bensmith1987

New Member

bensmith1987

New Member

MARK858

MrExcel MVP

bensmith1987

New Member

Similar threads

Forum statistics

Share this page

GMT & UTC time ?

New Member

Excel Facts

MrExcel MVP

New Member

New Member

New Member

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock