Correct date from time

[Frigide]

For a shift recording sheet i need date and time to set every 8 ours. Therefor i made a date and time cel that we fill in.
The time is from the "dienst" cell. It wil generate the time in the cells.
Ochtend 6:00, 7:00 8:00 9"00
Middag 14 15 16 etc.

But the date isn't looking at time. So in the night it wil not turn to the following day. Is there a better way to do this?

Datum-tijd.jpg

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Roel Jongman]

make a formula that adds 1 to the timevalue when the endtime is smaller then the begin time so that it does move to next day.

Based on that screenshot it is impossible to tell what the formula should be exactly.

[Frigide]

As you wish

[Pepe Le Mokko]

Please post on the forum. External links are unsafe and not all our members can access them. Thanks

[Frigide]

Sorry but how?

[Pepe Le Mokko]

Please read post #2, last sentence

[Frigide]

Like this? Fancy forum!

[Roel Jongman]

The solution is simple:

for cell A7 add the If formula from A8 to that cell
for Cell b7 do = A7+1/24 and drag to right
in row 8 put reference to row c7 and change format to show time only.

Than date will change correctly.
remember that a date is nothing more than a whole number and time is a fraction of 1. so you can do normal calculations addin 1/24 each cell will turnover the date if you add 1/24 to 23:00

reformat the cells to number to see the true values of dates and times. showing as date is purely a formatting mask on a number. Where you decide yourself what you want to show as value. only date, only time or combination of date and time

[Frigide]

Thank dude. How to Solve a topic?

[Roel Jongman]

Just below the topic title of the opening post there is a gray bar with a menu called "Thread tools" from that menu select Solved.

[Frigide]

Is there a way to make the date and the first time in the sheet in one cell. So date en time in one cell

[Frigide]

The solution is simple:

for cell A7 add the If formula from A8 to that cell
for Cell b7 do = A7+1/24 and drag to right
in row 8 put reference to row c7 and change format to show time only.

Than date will change correctly.
remember that a date is nothing more than a whole number and time is a fraction of 1. so you can do normal calculations addin 1/24 each cell will turnover the date if you add 1/24 to 23:00

reformat the cells to number to see the true values of dates and times. showing as date is purely a formatting mask on a number. Where you decide yourself what you want to show as value. only date, only time or combination of date and time

Is there a way to make the date and the first time in the sheet in one cell. So date en time in one cell

[Roel Jongman]

in my sheet in cel A7 date and time are already in 1 cel,
on A8 there is also date and time in one cell but I choose to present only time there

All I did is apply a format mask to only show the date or time so that is simple to change.
If you go to celproperties (cel eigenschappen) you can pick an appropiate date format that also has the time showing. so it is all in one cell