bluescan
05-20-2016, 11:31 PM
Good morning forum!
I am currently working on a day planner for my company. Tasks to be completed at specific times during the day are assigned to teams. This is then drawn graphically on a timeline for the day. So far it has been working great, but so far the day in my sheet has started at 00:00 and ended at 23:59. Now I need to be able to overlap between days. Example: The first shift starts 04:00 and the last shift ends at 03:00 the next day. Therefore, the plan must start at 04:00 and end 03:00 the next day.
Now I run into problems with Excel and dates. In my previous versions, all time formats have been formatted as "hh:mm". My first try has been to write f.ex. 03:00 the next day as 27:00, and using "[hh]:mm" as formatting but it turned out not to be that simple. On the sheet 27:00 is shown, but when I select the cell the value actually is "1/1/1900 03:00". If I select 23:00 on the sheet, it's value is just "23:00". They have all been formatted the same way, with "[hh]:mm". It also seems like VBA actually picks up "27:00" as "31/12/1899 03:00" when I see values during debug ( I guess this is the basic start date + 27 hours).
This issue has caused a lot of frustration and I find my self a bit stuck.
Do anybody have a good suggestion on how to handle "things after mid-night" in a simple matter?
Best regards,
Bluescan
I am currently working on a day planner for my company. Tasks to be completed at specific times during the day are assigned to teams. This is then drawn graphically on a timeline for the day. So far it has been working great, but so far the day in my sheet has started at 00:00 and ended at 23:59. Now I need to be able to overlap between days. Example: The first shift starts 04:00 and the last shift ends at 03:00 the next day. Therefore, the plan must start at 04:00 and end 03:00 the next day.
Now I run into problems with Excel and dates. In my previous versions, all time formats have been formatted as "hh:mm". My first try has been to write f.ex. 03:00 the next day as 27:00, and using "[hh]:mm" as formatting but it turned out not to be that simple. On the sheet 27:00 is shown, but when I select the cell the value actually is "1/1/1900 03:00". If I select 23:00 on the sheet, it's value is just "23:00". They have all been formatted the same way, with "[hh]:mm". It also seems like VBA actually picks up "27:00" as "31/12/1899 03:00" when I see values during debug ( I guess this is the basic start date + 27 hours).
This issue has caused a lot of frustration and I find my self a bit stuck.
Do anybody have a good suggestion on how to handle "things after mid-night" in a simple matter?
Best regards,
Bluescan