Calendar days and 'work' days

[gregp63]

Sure we've done this before but it's something I have to do every now and again and can never remember a simple way to do it from one time to the next:

'copper days' start at 6am - so 'today' doesn't finish until 5:59 tomorrow morning -

24/12/18 03:00:00 is really 23/12/18

24/12/18 15:00:00 is 24/12/18

25/12/18 03:00:00 is also 24/12/18

Anyone got a quick way to convert 'real' datetimes to 'copper' dates?

 

[steve the fish]

=int(a1-(6/24))

 

[gregp63]

=int(a1-(6/24))

Brilliant. Rather simpler than my deeply over-engineered alternative. Thanks.

 

[steve the fish]

Its simply removing 6 hours from your date time cell. You could just delete 0.25 but i did it that way to make it easier to see what is happening. Its simple when you understand that a day is 1 to excel so time is a fraction of 1.